﻿Imports System.Data
Imports System
Imports System.IO

Partial Class Content_Reportes_rep_controls_ReporteRendimiento
    Inherits System.Web.UI.UserControl
    Dim ECConnString As String = ConfigurationManager.AppSettings("dbIP")
    Dim ECConnString2 As New SqlClient.SqlConnection(ConfigurationManager.AppSettings("dbIP"))
    Dim IControles As New Clases.controles
    Dim IDataBase As New Clases.DataBase

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not IsPostBack Then
            'Dim q1, q2 As String
            'q1 = "Select cl_id, cl_nombre + ' ' + cl_apellido_p + ' ' + cl_apellido_m as nombre from TClientes where stat_id=1"
            'IControles.fill_dropDownListBaseQuery(ddlCliente, "TClientes", "cl_id", "nombre", q1)
            'IControles.addNoneAllElement(ddlCliente)

            'q2 = "Select * from TInstituciones where stat_id=1"
            'IControles.fill_dropDownListBaseQuery(ddlInstitucion, "TInstituciones", "inst_id", "inst_nombre", q2)
            'IControles.addNoneAllElement(ddlInstitucion)

            IControles.fill_dropDownListBaseQuery(ddlDespacho, "vwClientes", "desp_id", "desp_nombre", "select distinct desp_id, desp_nombre from vwclientes")

            IControles.addNoneAllElement(ddlDespacho)
            IControles.addNoneAllElement(ddlInstitucion)
            IControles.addNoneAllElement(ddlCliente)

            txtFechaI.Attributes.Add("onkeypress", "return false")
            txtFechaI.Attributes.Add("onPaste", "return false;")
            txtFechaF.Attributes.Add("onkeypress", "return false")
            txtFechaF.Attributes.Add("onPaste", "return false;")

            txtFechaF.Text = Request.Form(txtFechaF.UniqueID)
            'hl_pdf.Visible = False
        End If
        RangeValidator1.MaximumValue = DateTime.Today.ToString("yyyy-MM-dd")
    End Sub

    Protected Sub ImageButton1_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles ImageButton1.Click
        Dim qryBusq As String
        Dim cartI, cartF As Double
        cartI = CType(salIni.Text.Replace(".", ","), Double)
        cartF = CType(salFin.Text.Replace(".", ","), Double)
        'SELECT c.NombreC as Nombre, c.cl_contrato as Contrato, (Select SUM(m.movi_monto) as depositos where m.movi_tipo_op = 'Deposito') as Depositos, (Select SUM(m.movi_monto) as Retiros where m.movi_tipo_op = 'Retiro') as Retiros, dbo.fnSaldoPromedio(9,'2012-12-01','2012-12-18',10000000) as SaldoP, (5000000-900000) as Utilidad, dbo.fnRendimiento(9,'2012-12-01','2012-12-18',1000000,900500) as Rendimiento  FROM vwMovimientos  m, vwClientes c where 1=1 and m.cl_id=c.cl_id and c.cl_id = 9 group by c.NombreC, c.cl_contrato, m.movi_tipo_op 
        'qryBusq = "SELECT fechaop,depositos,retiros FROM vwRendimiento where 1=1"        
        'qryBusq = "SELECT c.NombreC as Nombre, c.cl_contrato as Contrato, sum(r.depositos) as Depositos, sum(r.retiros) as Retiros, (" & cartI & "- " & cartF & ") as Utilidad, dbo.fnSaldoPromedio(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "'," & salFin.Text & ") as SaldoP, dbo.fnRendimiento(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "'," & salIni.Text & "," & salFin.Text & ") as Rendimiento  FROM vwRendimiento r, vwClientes c where 1=1 and r.cl_id=c.cl_id and c.cl_id=" & ddlCliente.SelectedValue & ""
        'qryBusq = "SELECT c.NombreC as Nombre, c.cl_contrato as Contrato, (Select SUM(m.movi_monto) as depositos where m.movi_tipo_op = 'Deposito') as Depositos, (Select SUM(m.movi_monto) as Retiros where m.movi_tipo_op = 'Retiro') as Retiros, dbo.fnSaldoPromedio(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "'," & salFin.Text & ") as SaldoP, (" & cartI & "- " & cartF & ") as Utilidad, dbo.fnRendimiento(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "'," & salIni.Text & "," & salFin.Text & ") as Rendimiento  FROM vwMovimientos  m, vwClientes c where 1=1 and m.cl_id=c.cl_id and c.cl_id = " & ddlCliente.SelectedValue & ""
        qryBusq = "SELECT TOP 1 c.NombreC as Nombre, c.cl_contrato as Contrato, dbo.fnVMovi(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "','Deposito') as Depositos, dbo.fnVMovi(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "','Retiro') as Retiros, (" & cartI & "- " & cartF & ") as Utilidad, dbo.fnSaldoPromedio(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "') as SaldoP, dbo.fnRendimiento(" & ddlCliente.SelectedValue & ",'" & txtFechaI.Text & "','" & txtFechaF.Text & "'," & salIni.Text.Replace(".", ",") & "," & salFin.Text.Replace(".", ",") & ") as Rendimiento  FROM vwMovimientos  m, vwClientes c where 1=1 and m.cl_id=c.cl_id and c.cl_id = " & ddlCliente.SelectedValue & ""
        'If ddlCliente.SelectedValue <> "None" Then
        '    qryBusq = qryBusq & " and cl_id=" & ddlCliente.SelectedValue & ""
        'End If
        If ddlDespacho.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and c.desp_id=" & ddlDespacho.SelectedValue & ""
        End If

        If ddlInstitucion.SelectedValue <> "None" Then
            qryBusq = qryBusq & " and c.inst_id=" & ddlInstitucion.SelectedValue & ""
        End If

        If (txtFechaI.Text <> "") And (txtFechaF.Text <> "") Then
            qryBusq = qryBusq & " and m.movi_fecha_op between '" & txtFechaI.Text & "' and '" & txtFechaF.Text & "'"
        End If

        If (txtFechaI.Text <> "") And (txtFechaF.Text = "") Then
            qryBusq = qryBusq & " and m.movi_fecha_op between '" & txtFechaI.Text & "' and getdate()"
        End If

        qryBusq = qryBusq & " group by c.NombreC, c.cl_contrato, m.movi_tipo_op "

        Session("RepRen") = qryBusq
        IControles.fillGridViewPaging(gvRendimiento, qryBusq)
        btn_excel.Visible = True
    End Sub

    Protected Sub exportToExcel(ByVal query As String)
        Dim gridView_toExport As New GridView
        IControles.fillGridViewPaging(gridView_toExport, Session("RepRen"))
        For i As Integer = 0 To gridView_toExport.Rows.Count - 1
            Dim row As GridViewRow = gridView_toExport.Rows(i)
        Next
        Dim sb As StringBuilder = New StringBuilder()
        Dim sw As IO.StringWriter = New IO.StringWriter(sb)
        Dim htw As HtmlTextWriter = New HtmlTextWriter(sw)
        Dim pagina As Page = New Page
        Dim form = New HtmlForm
        pagina.EnableEventValidation = False
        pagina.DesignerInitialize()
        pagina.Controls.Add(form)
        gridView_toExport.AllowPaging = False
        form.Controls.Add(gridView_toExport)
        pagina.RenderControl(htw)
        Response.Clear()
        Response.Buffer = True
        Response.ContentType = "application/vnd.ms-excel"
        Response.AddHeader("Content-Disposition", "attachment;filename=rendimiento" & Now.ToString("_dd_MMMM_yy_HH_mm_ss") & ".xls")
        Response.Charset = "UTF-8"
        Response.ContentEncoding = Encoding.Default
        Response.Write(sb.ToString())
        Response.End()
    End Sub

    Protected Sub btn_excel_Click(ByVal sender As Object, ByVal e As System.Web.UI.ImageClickEventArgs) Handles btn_excel.Click
        exportToExcel(Session("RepRen"))
    End Sub

    Protected Sub ddlDespacho_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlDespacho.SelectedIndexChanged
        'Protected Sub ddlDespacho_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlDespacho.SelectedIndexChanged
        If ddlDespacho.SelectedValue = "None" Then
            ddlInstitucion.Items.Clear()
            IControles.addNoneAllElement(ddlInstitucion)
        ElseIf ddlDespacho.SelectedValue <> "None" Then
            Dim query As String = "select distinct inst_id, inst_nombre from vwClientes where desp_id =" & ddlDespacho.SelectedValue
            IControles.fill_dropDownListBaseQuery(ddlInstitucion, "vwClientes", "inst_id", "inst_nombre", query)

            IControles.addNoneAllElement(ddlInstitucion)
        End If
    End Sub

    Protected Sub ddlInstitucion_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlInstitucion.SelectedIndexChanged
        'Protected Sub ddlInstitucion_SelectedIndexChanged(sender As Object, e As EventArgs) Handles ddlInstitucion.SelectedIndexChanged
        If ddlInstitucion.SelectedValue = "None" Then
            ddlCliente.Items.Clear()
            IControles.addNoneAllElement(ddlCliente)
        ElseIf ddlInstitucion.SelectedValue <> "None" Then
            Dim query As String = "select distinct cl_id, nombrec + ' - ' + cl_contrato as nombre from vwClientes where inst_id = " & ddlInstitucion.SelectedValue
            IControles.fill_dropDownListBaseQuery(ddlCliente, "vwClientes", "cl_id", "nombre", query)

            IControles.addNoneAllElement(ddlCliente)
        End If
    End Sub

    Private Sub ValFechas()
        RangeValidator1.MinimumValue = "01/01/1900"
        RangeValidator1.MaximumValue = DateTime.Today.ToString("yyyy-MM-dd")
        RangeValidator1.ErrorMessage = "La fecha del operación no puede ser mayor al día de Hoy"
    End Sub
End Class
